from PyQt5.QtWidgets import QApplication, QMainWindow,QWidget,QTableWidgetItem,QMessageBox
from PyQt5.uic import loadUiType
import sys
import pymysql


#####################################动态加载UI文件，实现UI与逻辑操作分离######################################################
# UI--Logic 分离 动态加载UI文件
ui, _ = loadUiType('denglu.ui')
login, _ = loadUiType('zhuce.ui')
zjm, _ = loadUiType('main_windows.ui')
#####################################登录界面###############################################################################
class dengluApp(QWidget,ui):
    def __init__(self):
        QWidget.__init__(self)
        self.setupUi(self)
        self.zhucepushButton_2.clicked.connect(self.zhuce)
        self.user_pushButton.clicked.connect(self.dlmain)
    def zhuce(self):
        self.a=LoginnApp()
        self.close()
        self.a.show()
    def dlmain(self):
        # 数据库操作流程
        # 1.获取连接
        db = pymysql.connect(host="localhost", user="root", passwd="745201", database="dazy",charset="utf8")  # 使用pymysql.connect来连接
        # 2.获取游标
        cur = db.cursor()
        # 3.SQL语句
        sql = "select * from user where user_name = %s and user_pwd = %s"
        user_name = self.username_lineEdit.text()  # 实现从指定界面输入框获取指定输入信息
        user_pwd = self.userpwd_lineEdit_2.text()
        cur.execute(sql, (user_name, user_pwd))
        date=cur.fetchone()
        if date:
            self.M=MainAPP()
            self.close()
            self.M.show()
        else:
            self.label_7.setText('密码错误！')


#####################################注册界面###############################################################################
class LoginnApp(QWidget,login):
    def __init__(self):
        QWidget.__init__(self)
        self.setupUi(self)
        self.handle()


    def handle(self):
        self.dlpushButton.clicked.connect(self.denglu)
        self.pushButton_zc.clicked.connect(self.zc_user)

    def denglu(self):
        self.b=dengluApp()
        self.close()
        self.b.show()

    def zc_user(self):
        # 数据库操作流程
        # 1.获取连接
        db = db = pymysql.connect(host="localhost", user="root", passwd="745201", database="dazy",charset="utf8")  # 使用pymysql.connect来连接
        # 2.获取游标
        cur = db.cursor()
        # 3.SQL语句
        sql = "insert into user(user_name,user_pwd) values(%s,%s)"
        a = self.zhuce_user_name.text()  # 实现从指定界面输入框获取指定输入信息
        c = self.zhuce_user_pwd.text()
        d = self.zhuce_user_pwd_qr.text()
        if c == d:

            cur.execute(sql, (a,c))
            db.commit()
            cur.close()
            db.close()
            self.label_5.setText("注册成功！")

        else:
            self.label_5.setText("两次密码不一致")


#####################################主界面界面###############################################################################
class MainAPP(QMainWindow,zjm):
    def __init__(self):
        QMainWindow.__init__(self)
        self.setupUi(self)
        self.yingcang()
        self.button()
        self.sp_zs()
    def button(self):
        self.sp_pushButton_3.clicked.connect(self.open_sp_tab)
        self.yh_pushButton_4.clicked.connect(self.open_yh_tab)
        self.dl_pushButton.clicked.connect(self.dl)
        self.changepwd_pushButton_2.clicked.connect(self.change_user)
        self.spsj_pushButton.clicked.connect(self.sp_sj)
        self.cx_pushButton.clicked.connect(self.sp_cx)
        self.cx_pushButton_2.clicked.connect(self.sp_xg)
        self.xg_pushButton.clicked.connect(self.sp_xg_1)
        self.xj_pushButton.clicked.connect(self.sp_xj)
#####################################商品管理界面############################################################################
    def open_sp_tab(self):
        self.tabWidget.setCurrentIndex(0)

#####################################用户管理界面############################################################################
    def open_yh_tab(self):
        self.tabWidget.setCurrentIndex(1)

#####################################隐藏界面############################################################################
    def yingcang(self):
        self.tabWidget.tabBar().setVisible(False)

#####################################用户管理界面查询用户############################################################################
    def dl(self):
        db = db = pymysql.connect(host="localhost", user="root", passwd="745201", database="dazy",charset="utf8")  # 使用pymysql.connect来连接
        cur = db.cursor()
        sql = "select * from user where user_name = %s and user_pwd = %s"
        user_nam = self.username1_lineEdit.text()  # 实现从指定界面输入框获取指定输入信息
        user_pw = self.userpwd1_lineEdit.text()
        cur.execute(sql, (user_nam, user_pw))
        date = cur.fetchone()
        if date:
            self.user_groupBox_2.setEnabled(True)
            self.username2_lineEdit_3.setText(date[1])

#####################################用户管理界面修改密码界面############################################################################
    def change_user(self):
        a = self.username1_lineEdit.text()
        b = self.username2_lineEdit_3.text()
        c = self.userpwd2_lineEdit_4.text()
        d = self.userpwd3_lineEdit_5.text()
        if c == d:
            db =db = pymysql.connect(host="localhost", user="root", passwd="745201", database="dazy",charset="utf8")  # 使用pymysql.connect来连接
            cur = db.cursor()
            sql = "update user set user_name =%s,user_pwd = %s where user_name = %s"
            cur.execute(sql,(b,c,a,))
            db.commit()
            cur.close()
            db.close()
            self.chang_label_6.setText('修改成功！')
        else:
            self.chang_label_6.setText('两次密码不一致！')
            self.userpwd2_lineEdit_4.setText('')
            self.userpwd3_lineEdit_5.setText('')
#####################################商品管理界面商品上架############################################################################
    def sp_sj(self):
        db = pymysql.connect(host="localhost", user="root", passwd="745201", database="dazy",charset="utf8")  # 使用pymysql.connect来连接
        cur = db.cursor()
        sql = "insert into sp_sj(sp_id,sp_name,sp_jg,sp_cd) values(%s,%s,%s,%s)"
        a = self.sp2_lineEdit_2.text()
        b = self.sp1_lineEdit.text()
        c = self.sp3_lineEdit_3.text()
        d = self.sp4_lineEdit_4.text()
        cur.execute(sql, (a,b,c,d,))
        db.commit()
        cur.close()
        db.close()
        self.sp_zs()
        self.statusBar().showMessage('商品上架成功!')

##############################################商品展示界面###################################################################
    def sp_zs(self):
        db = pymysql.connect(host="localhost", user="root", passwd="745201", database="dazy",charset="utf8")  # 使用pymysql.connect来连接
        cur = db.cursor()
        sql = "select * from sp_sj"
        cur.execute(sql)
        data = cur.fetchall()    # 获取查询到的数据，是以二维元组的形式存储的，所以读取需要使用data[i][j] 下标定位
        print(data)
        if data:
            self.tableWidget_spzs.setRowCount(0)
            self.tableWidget_spzs.insertRow(0)
            for row, form in enumerate(data):
                for column, item in enumerate(form):
                    self.tableWidget_spzs.setItem(row, column, QTableWidgetItem(str(item)))
                    column += 1
                row_position = self.tableWidget_spzs.rowCount()
                self.tableWidget_spzs.insertRow(row_position)
##############################################商品查询界面###################################################################
    def sp_cx(self):
        db = pymysql.connect(host="localhost", user="root", passwd="745201", database="dazy",charset="utf8")  # 使用pymysql.connect来连接
        cur = db.cursor()
        sql = "select * from sp_sj where sp_id=%s"
        sp_id = self.sp2_lineEdit_5.text()

        cur.execute(sql, (sp_id))
        data = cur.fetchone()
        if data:
            self.cx_lineEdit1.setText(str(data[0]))
            self.cx_lineEdit2.setText(data[1])
            self.cx_lineEdit3.setText(str(data[2]))
            self.cx_lineEdit4.setText(str(data[3]))
##############################################商品信息修改界面###################################################################
#首先查询出需要做修改的商品信息
    def sp_xg(self):
        db = pymysql.connect(host="localhost", user="root", passwd="745201", database="dazy",charset="utf8")  # 使用pymysql.connect来连接
        cur = db.cursor()
        sql = "select * from sp_sj where sp_id=%s"
        sp_id = self.sp2_lineEdit_6.text()

        cur.execute(sql, (sp_id))
        data = cur.fetchone()
        if data:
            self.groupBox_3.setEnabled(True)
            self.cx_lineEdit_1.setText(str(data[0]))
            self.cx_lineEdit_2.setText(data[1])
            self.cx_lineEdit_3.setText(str(data[2]))
            self.cx_lineEdit_4.setText(str(data[3]))

#对查询出来的商品信息进行修改
    def sp_xg_1(self):
        id = self.sp2_lineEdit_6.text()
        a = self.cx_lineEdit_1.text()
        b = self.cx_lineEdit_2.text()
        c = self.cx_lineEdit_3.text()
        d = self.cx_lineEdit_4.text()
        db = pymysql.connect(host="localhost", user="root", passwd="745201", database="dazy",charset="utf8")  # 使用pymysql.connect来连接
        cur = db.cursor()
        sql = "update sp_sj set sp_id=%s,sp_name=%s,sp_jg=%s,sp_cd=%s where sp_id=%s"
        cur.execute(sql, (a, b, c, d, id))
        self.label_7.setText('修改成功！')
        db.commit()
        cur.close()
        db.close()
        self.sp_zs()
#对查询出来的商品进行下架
    def sp_xj(self):
        id = self.sp2_lineEdit_6.text()
        warning = QMessageBox.warning(self, "删除商品信息", "您确定删除吗？", QMessageBox.Yes | QMessageBox.No)
        if warning == QMessageBox.Yes:
            db = pymysql.connect(host="localhost", user="root", passwd="745201", database="dazy",charset="utf8")  # 使用pymysql.connect来连接
            cur = db.cursor()
            sql = "delete from sp_sj where sp_id=%s"
            cur.execute(sql, (id))
            db.commit()
            cur.close()
            db.close()
            self.sp_zs()

#####################################################################################################################
def main():
    app = QApplication([])     # 创建应用程序 创建一个QApplication，也就是你要开发的软件app
    window = dengluApp()         # 创建主窗口  创建一个MainAPP，用来装载你需要的各种组件、控件
    window.show()              # 显示窗口 执行QMainWindow的show()方法，显示这个QMainWindow
    app.exec_()                # 使用exit()或者点击关闭按钮退出QApplication

if __name__ == '__main__':

        main()

